package cn.scnu.service.impl;

import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.io.FileUtil;
import cn.hutool.core.io.resource.ResourceUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.core.util.ZipUtil;
import cn.scnu.common.model.Pagination;
import cn.scnu.common.model.PaginationData;
import cn.scnu.common.util.GisUtil;
import cn.scnu.component.exception.ApiException;
import cn.scnu.mapper.ProjectAcceptanceMapper;
import cn.scnu.mapper.ProjectAchievementGeneralPlanMapper;
import cn.scnu.mapper.ProjectAchievementMapper;
import cn.scnu.model.dto.*;
import cn.scnu.model.entity.ProjectAcceptanceEntity;
import cn.scnu.model.entity.ProjectAchievementEntity;
import cn.scnu.model.entity.ProjectAchievementGeneralPlanEntity;
import cn.scnu.model.enums.TableNameEnum;
import cn.scnu.model.query.FuzzySearchQueryParam;
import cn.scnu.model.query.GetDataQueryParam;
import cn.scnu.model.query.MonthCountQueryParam;
import cn.scnu.model.query.ProjectResultQueryParam;
import cn.scnu.service.IProjectAcceptanceService;
import com.alibaba.excel.EasyExcel;
import com.baomidou.dynamic.datasource.annotation.DS;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import lombok.RequiredArgsConstructor;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import org.jetbrains.annotations.NotNull;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;
import java.io.File;
import java.io.IOException;
import java.net.URLEncoder;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;
import java.util.stream.Collectors;

/**
 * <p>
 * 服务实现类
 * </p>
 *
 * @author hqzqaq
 * @since 2024-11-22
 */
@Slf4j
@Service
@RequiredArgsConstructor
@DS("master_2")
public class ProjectAcceptanceServiceImpl extends ServiceImpl<ProjectAcceptanceMapper, ProjectAcceptanceEntity> implements IProjectAcceptanceService {

    private final ProjectAcceptanceMapper mapper;

    private final ProjectAchievementMapper achievementMapper;

    private final ProjectAchievementGeneralPlanMapper generalPlanMapper;

    private final DataSource dataSource;

    @Value("${shp.path}")
    private String shpPath;

    @Value("${shp.charsetName}")
    private String charsetName;

    @Override
    public Double getJdmjCount() {
        return mapper.getJdmjCount();
    }

    @Override
    public Double getZjzmjCount() {
        return mapper.getZjzmjCount();
    }

    @Override
    public Double getJzgdCount() {
        return mapper.getJzgdCount();
    }

    @SneakyThrows
    @Override
    public <T> PaginationData<T> getProjectResultPage(ProjectResultQueryParam queryParam) {
        if (queryParam.getTableName().equals(TableNameEnum.PROJECT_ACCEPTANCE)) {
            QueryWrapper<ProjectAcceptanceEntity> queryWrapper = buildQueryWrapper(queryParam, ProjectAcceptanceEntity.class, false);
            Page<ProjectAcceptanceEntity> entityPage = mapper.selectPage(new Page<>(queryParam.getPageNo(), queryParam.getPageSize()), queryWrapper);
            List<T> dtoList = (List<T>) entityPage.getRecords().stream().map(entity -> po2Dto(entity, ProjectAcceptanceDTO.class)).collect(Collectors.toList());
            return new PaginationData<>(dtoList, new Pagination(entityPage));

        } else if (queryParam.getTableName().equals(TableNameEnum.PROJECT_ACHIEVEMENT)) {
            QueryWrapper<ProjectAchievementEntity> queryWrapper = buildQueryWrapper(queryParam, ProjectAchievementEntity.class, false);
            Page<ProjectAchievementEntity> entityPage = achievementMapper.selectPage(new Page<>(queryParam.getPageNo(), queryParam.getPageSize()), queryWrapper);
            List<T> dtoList = (List<T>) entityPage.getRecords().stream().map(entity -> po2Dto(entity, ProjectAcceptanceDTO.class)).collect(Collectors.toList());
            return new PaginationData<>(dtoList, new Pagination(entityPage));

        } else {
            QueryWrapper<ProjectAchievementGeneralPlanEntity> queryWrapper = buildQueryWrapper(queryParam, ProjectAchievementGeneralPlanEntity.class, false);
            Page<ProjectAchievementGeneralPlanEntity> entityPage = generalPlanMapper.selectPage(new Page<>(queryParam.getPageNo(), queryParam.getPageSize()), queryWrapper);
            List<T> dtoList = (List<T>) entityPage.getRecords().stream().map(entity -> po2Dto(entity, ProjectAchievementGeneralPlanDTO.class)).collect(Collectors.toList());
            return new PaginationData<>(dtoList, new Pagination(entityPage));
        }
    }

    private static <T> @NotNull QueryWrapper<T> buildQueryWrapper(ProjectResultQueryParam queryParam, Class<T> entityClass, Boolean isGetGeometry) {
        QueryWrapper<T> queryWrapper = new QueryWrapper<>();
        if (!isGetGeometry) {
            queryWrapper.select(entityClass, info -> !info.getColumn().equals("smgeometry"));
        }

        // Handle time range filters
        queryWrapper.ge(queryParam.getWcsjStartTime() != null, "wcsj", queryParam.getWcsjStartTime());
        queryWrapper.le(queryParam.getWcsjEndTime() != null, "wcsj", queryParam.getWcsjEndTime());

        if (queryParam.getFieldMapOperates() != null) {
            // Handle field-based filters
            queryParam.getFieldMapOperates().forEach(fieldMapOperate -> {
                switch (fieldMapOperate.getOperate()) {
                    case EQUALS:
                        queryWrapper.eq(fieldMapOperate.getField(), fieldMapOperate.getValue());
                        break;
                    case NOT_EQUALS:
                        queryWrapper.ne(fieldMapOperate.getField(), fieldMapOperate.getValue());
                        break;
                    case GREATER_THAN:
                        queryWrapper.gt(fieldMapOperate.getField(), fieldMapOperate.getValue());
                        break;
                    case LESS_THAN:
                        queryWrapper.lt(fieldMapOperate.getField(), fieldMapOperate.getValue());
                        break;
                    case GREATER_THAN_OR_EQUALS:
                        queryWrapper.ge(fieldMapOperate.getField(), fieldMapOperate.getValue());
                        break;
                    case LESS_THAN_OR_EQUALS:
                        queryWrapper.le(fieldMapOperate.getField(), fieldMapOperate.getValue());
                        break;
                    case CONTAINS:
                        queryWrapper.like(fieldMapOperate.getField(), fieldMapOperate.getValue());
                        break;
                    case ORDER_ASC:
                        queryWrapper.orderByAsc(fieldMapOperate.getField());
                        break;
                    case ORDER_DESC:
                        queryWrapper.orderByDesc(fieldMapOperate.getField());
                        break;
                    default:
                        // Handle unsupported operations or invalid input
                        throw new ApiException("Unsupported query operation: " + fieldMapOperate.getOperate());
                }
            });
        }

        // 绘制范围的空间查询处理
        if (StrUtil.isNotBlank(queryParam.getWktRange())) {
            queryWrapper.apply(StrUtil.format("ST_Intersects(smgeometry, ST_GeomFromText('{}',{}))", queryParam.getWktRange(), 32768));
        }
        // 行政区查询处理
        if (StrUtil.isNotBlank(queryParam.getWktDistrict())) {
            queryWrapper.apply(StrUtil.format("ST_Intersects(smgeometry, ST_GeomFromText('{}',{}))", queryParam.getWktDistrict(), 32768));
        }
        // shp文件查询处理
        if (queryParam.getWktShpRange() != null) {
            for (String wkt : queryParam.getWktShpRange()) {
                queryWrapper.or(i -> i.apply(StrUtil.format("ST_Intersects(smgeometry, ST_GeomFromText('{}',{}))", wkt, 32768)));
            }
        }
        // cad文件查询处理
        if (StrUtil.isNotBlank(queryParam.getWktCadRange())) {
            queryWrapper.apply(StrUtil.format("ST_Intersects(smgeometry, ST_GeomFromText('{}',{}))", queryParam.getWktCadRange(), 32768));
        }
        return queryWrapper;
    }

    private static List<String> processShpFile(MultipartFile shpFile) {
        // MultipartFile 转为 file 保存到本地
        // 先检查文件是否为zip压缩文件
        String name = shpFile.getOriginalFilename();
        String suffix = FileUtil.getSuffix(name);
        if (!suffix.equals("zip")) {
            throw new ApiException("请上传zip文件");
        }
        // 保存到本地临时文件中，使用hutool工具类
        File unzipDir = FileUtil.mkdir("/temp");
        File tempFile = FileUtil.createTempFile(new File("/temp/" + name));
        try {
            shpFile.transferTo(tempFile);
        } catch (IOException e) {
            throw new ApiException("文件保存失败", e);
        }
        // 使用解压文件，使用hutool工具类
        ZipUtil.unzip(tempFile, unzipDir);
        // 读取shp文件
        File[] shpFiles = FileUtil.loopFiles(unzipDir, pathname -> pathname.getName().endsWith(".shp")).toArray(new File[0]);
        if (shpFiles.length == 0) {
            throw new ApiException("未找到shp文件");
        }
        // 读取shp文件内容
        File shpFile1 = shpFiles[0];
        List<String> wktFromShp = GisUtil.getWktFromShp(shpFile1);
        if (wktFromShp.isEmpty()) {
            throw new ApiException("shp文件内容为空");
        }
        FileUtil.del("/temp");
        return wktFromShp;
    }

    private <E> E po2Dto(ProjectAcceptanceEntity entity, Class<E> dtoClass) {
        return BeanUtil.toBean(entity, dtoClass);
    }

    private <T> T po2Dto(ProjectAchievementGeneralPlanEntity entity, Class<T> dtoClass) {
        return BeanUtil.toBean(entity, dtoClass);
    }

    private <E> E po2Dto(ProjectAchievementEntity entity, Class<E> dtoClass) {
        E bean = BeanUtil.toBean(entity, dtoClass);
        if (bean instanceof ProjectAcceptanceDTO) {
            ((ProjectAcceptanceDTO) bean).setJrjzmj(entity.getZjrjzmj());
        }
        return bean;
    }

    @Override
    public List<String> getFwgnList(String fwgn) {
        return mapper.selectDistinctFwgn(fwgn);
    }

    @Override
    public List<String> getJzjgList() {
        return mapper.selectDistinctJzjg();
    }

    @Override
    public List<String> getSearchList(FuzzySearchQueryParam queryParam) {
        if (queryParam.getTableName().equals(TableNameEnum.PROJECT_ACCEPTANCE)) {
            QueryWrapper<ProjectAcceptanceEntity> queryWrapper = new QueryWrapper<>();
            queryWrapper.select(StrUtil.format("DISTINCT {}", queryParam.getField())).like(queryParam.getField(), queryParam.getValue());
            return mapper.selectObjs(queryWrapper).stream().map(Object::toString).collect(Collectors.toList());
        } else if (queryParam.getTableName().equals(TableNameEnum.PROJECT_ACHIEVEMENT_GENERAL_PLAN)) {
            QueryWrapper<ProjectAchievementGeneralPlanEntity> queryWrapper = new QueryWrapper<>();
            queryWrapper.select(StrUtil.format("DISTINCT {}", queryParam.getField())).like(queryParam.getField(), queryParam.getValue());
            return generalPlanMapper.selectObjs(queryWrapper).stream().map(Object::toString).collect(Collectors.toList());
        } else {
            QueryWrapper<ProjectAchievementEntity> queryWrapper = new QueryWrapper<>();
            queryWrapper.select(StrUtil.format("DISTINCT {}", queryParam.getField())).like(queryParam.getField(), queryParam.getValue());
            return achievementMapper.selectObjs(queryWrapper).stream().map(Object::toString).collect(Collectors.toList());
        }
    }

    @Override
    public Map<String, String> getTableColumns() {
        Map<String, String> columns = new HashMap<>();
        try (Connection connection = dataSource.getConnection()) {
            DatabaseMetaData metaData = connection.getMetaData();
            try (ResultSet resultSet = metaData.getColumns(null, null, "工程成果_验收", null)) {
                while (resultSet.next()) {
                    String columnName = resultSet.getString("COLUMN_NAME");
                    String columnType = resultSet.getString("TYPE_NAME");
                    String columnComment = resultSet.getString("REMARKS");
                    columns.put(columnName, columnType + (columnComment != null ? "," + columnComment : ""));
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return columns;
    }

    @SneakyThrows
    @Override
    public List<BaiYunRegionAttrDTO> getBaiyunAttr() {
        return GisUtil.getAttrFromGeoJson(ResourceUtil.getStream(shpPath), BaiYunRegionAttrDTO.class);
    }

    @Override
    public String getWktForSmid(TableNameEnum tableName, Integer smid) {
        if (tableName.equals(TableNameEnum.PROJECT_ACCEPTANCE)) {
            return mapper.getWktForSmid(smid);
        } else {
            return achievementMapper.getWktForSmid(smid);
        }
    }

    @Override
    public List<String> getWktForSmids(TableNameEnum tableName, List<Integer> smids) {
        if (smids.isEmpty()) {
            return new ArrayList<>();
        }
        if (tableName.equals(TableNameEnum.PROJECT_ACCEPTANCE)) {
            return mapper.getWktForSmids(smids);
        } else {
            return achievementMapper.getWktForSmids(smids);
        }
    }

    @Override
    public List<String> getWktForUploadShp(MultipartFile shpFile) {
        return processShpFile(shpFile);
    }

    @Override
    @SneakyThrows
    public void exportProjectResultExcel(ProjectResultQueryParam queryParam, HttpServletResponse response) {
        // 设置响应头
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("UTF-8");
        String fileName = queryParam.getTableName().getTableName() + System.currentTimeMillis() + ".xlsx";
        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
        List<ProjectAcceptanceDTO> collect;
        // 获取分页数据
        if (queryParam.getTableName().equals(TableNameEnum.PROJECT_ACCEPTANCE)) {
            List<ProjectAcceptanceEntity> projectAcceptanceEntities = mapper.selectList(buildQueryWrapper(queryParam, ProjectAcceptanceEntity.class, false));
            collect = projectAcceptanceEntities.stream().map(entity -> this.po2Dto(entity, ProjectAcceptanceDTO.class)).collect(Collectors.toList());
        } else if (queryParam.getTableName().equals(TableNameEnum.PROJECT_ACHIEVEMENT)) {
            List<ProjectAchievementEntity> projectAchievementEntities = achievementMapper.selectList(buildQueryWrapper(queryParam, ProjectAchievementEntity.class, false));
            collect = projectAchievementEntities.stream().map(entity -> this.po2Dto(entity, ProjectAcceptanceDTO.class)).collect(Collectors.toList());
        } else {
            List<ProjectAchievementGeneralPlanEntity> projectAchievementGeneralPlanEntities = generalPlanMapper.selectList(buildQueryWrapper(queryParam, ProjectAchievementGeneralPlanEntity.class, false));
            List<ProjectAchievementGeneralPlanDTO> generalPlanDTOList = projectAchievementGeneralPlanEntities.stream().map(entity -> this.po2Dto(entity, ProjectAchievementGeneralPlanDTO.class)).collect(Collectors.toList());
            // 创建 Excel 写入器
            EasyExcel.write(response.getOutputStream(), ProjectAcceptanceDTO.class)
                    .sheet(queryParam.getTableName().getTableName())
                    .doWrite(generalPlanDTOList);
            return;
        }

        // 创建 Excel 写入器
        EasyExcel.write(response.getOutputStream(), ProjectAcceptanceDTO.class)
                .sheet(queryParam.getTableName().getTableName())
                .doWrite(collect);
    }

    @Override
    public ProjectResultIndexDTO getProjectResultIndex(ProjectResultQueryParam queryParam) {
        ProjectResultIndexDTO projectResultIndexDTO = new ProjectResultIndexDTO();
        if (queryParam.getTableName().equals(TableNameEnum.PROJECT_ACCEPTANCE)) {
            QueryWrapper<ProjectAcceptanceEntity> queryWrapper = buildQueryWrapper(queryParam, ProjectAcceptanceEntity.class, false);
            // mybats-plus 定义查询的字段
            queryWrapper.select("sum(zjzmj) as zjzmj_sum", "sum(jrjzmj) as jrjzmj_sum", "sum(jdmj) as jdmj_sum", "count(1) as ds_sum", "max(jzgd) as lg_max", "max(dscs) as cs_max");
            buildProjectResult(projectResultIndexDTO, mapper.selectMaps(queryWrapper));
        } else {
            QueryWrapper<ProjectAchievementEntity> queryWrapper = buildQueryWrapper(queryParam, ProjectAchievementEntity.class, false);
            // mybats-plus 定义查询的字段
            queryWrapper.select("sum(zjzmj) as zjzmj_sum", "sum(zjrjzmj) as jrjzmj_sum", "sum(jdmj) as jdmj_sum", "count(1) as ds_sum", "max(jzgd) as lg_max", "max(dscs) as cs_max");
            buildProjectResult(projectResultIndexDTO, achievementMapper.selectMaps(queryWrapper));
        }
        return projectResultIndexDTO;
    }

    private void buildProjectResult(ProjectResultIndexDTO projectResultIndexDTO, List<Map<String, Object>> maps) {
        maps.forEach(map -> {
            projectResultIndexDTO.setZjzmjSum((Double) map.get("zjzmj_sum"));
            projectResultIndexDTO.setJrjzmjSum((Double) map.get("jrjzmj_sum"));
            projectResultIndexDTO.setJdmjSum((Double) map.get("jdmj_sum"));
            projectResultIndexDTO.setDsSum((Long) map.get("ds_sum"));
            projectResultIndexDTO.setLgMax((Double) map.get("lg_max"));
            projectResultIndexDTO.setCsMax((Integer) map.get("cs_max"));
        });
    }

    @SneakyThrows
    @Override
    public List<BaiYunRegionCountDTO> getCountByDistrict(ProjectResultQueryParam queryParam) {
        List<BaiYunRegionAttrDTO> attrFromShp = GisUtil.getAttrFromGeoJson(ResourceUtil.getStream(shpPath), BaiYunRegionAttrDTO.class);

        if (queryParam.getTableName().equals(TableNameEnum.PROJECT_ACCEPTANCE)) {
            return attrFromShp.stream().map(attr -> {
                BaiYunRegionCountDTO baiYunRegionCountDTO = new BaiYunRegionCountDTO();
                BeanUtil.copyProperties(attr, baiYunRegionCountDTO, "geometry");
                QueryWrapper<ProjectAcceptanceEntity> queryWrapper = buildQueryWrapper(queryParam, ProjectAcceptanceEntity.class, false);
                queryWrapper.select("COALESCE(sum(zjzmj), 0) as zjzmj_sum", "count(1) as ds_sum");
                queryWrapper.apply(StrUtil.format("ST_Intersects(smgeometry, ST_GeomFromText('{}',{}))", attr.getGeometry(), 32768));
                mapper.selectMaps(queryWrapper).forEach(map -> {
                    baiYunRegionCountDTO.setZjzmjSum((Double) map.get("zjzmj_sum"));
                    baiYunRegionCountDTO.setDsSum((Long) map.get("ds_sum"));
                });
                return baiYunRegionCountDTO;
            }).collect(Collectors.toList());
        } else {
            return attrFromShp.stream().map(attr -> {
                BaiYunRegionCountDTO baiYunRegionCountDTO = new BaiYunRegionCountDTO();
                BeanUtil.copyProperties(attr, baiYunRegionCountDTO, "geometry");
                QueryWrapper<ProjectAchievementEntity> queryWrapper = buildQueryWrapper(queryParam, ProjectAchievementEntity.class, false);
                queryWrapper.select("COALESCE(sum(zjzmj), 0) as zjzmj_sum", "count(1) as ds_sum");
                queryWrapper.apply(StrUtil.format("ST_Intersects(smgeometry, ST_GeomFromText('{}',{}))", attr.getGeometry(), 32768));
                achievementMapper.selectMaps(queryWrapper).forEach(map -> {
                    baiYunRegionCountDTO.setZjzmjSum((Double) map.get("zjzmj_sum"));
                    baiYunRegionCountDTO.setDsSum((Long) map.get("ds_sum"));
                });
                return baiYunRegionCountDTO;
            }).collect(Collectors.toList());
        }

    }

    @Override
    public List<ZjzmjMonthDTO> getMonthCountForZjzmj(MonthCountQueryParam queryParam) {
        List<ZjzmjMonthDTO> result = new ArrayList<>();
        List<ZjzmjMonthDTO> monthCounts;
        if (queryParam.getTableName().equals(TableNameEnum.PROJECT_ACCEPTANCE)) {
            QueryWrapper<ProjectAcceptanceEntity> queryWrapper = buildQueryWrapper(queryParam, ProjectAcceptanceEntity.class, false);
            queryWrapper.select("EXTRACT(MONTH FROM wcsj) AS key", "sum(zjzmj) as value");
            queryWrapper.apply(StrUtil.format("EXTRACT(YEAR FROM wcsj) = {}", queryParam.getYear()));
            queryWrapper.groupBy("key");
            monthCounts = mapper.selectMaps(queryWrapper).stream()
                    .map(map -> new ZjzmjMonthDTO(((Number) map.get("key")).intValue(), (Double) map.get("value")))
                    .collect(Collectors.toList());
        } else {
            QueryWrapper<ProjectAchievementEntity> queryWrapper = buildQueryWrapper(queryParam, ProjectAchievementEntity.class, false);
            queryWrapper.select("EXTRACT(MONTH FROM wcsj) AS key", "sum(zjzmj) as value");
            queryWrapper.apply(StrUtil.format("EXTRACT(YEAR FROM wcsj) = {}", queryParam.getYear()));
            queryWrapper.groupBy("key");
            monthCounts = achievementMapper.selectMaps(queryWrapper).stream()
                    .map(map -> new ZjzmjMonthDTO(((Number) map.get("key")).intValue(), (Double) map.get("value")))
                    .collect(Collectors.toList());
        }
        Map<Integer, ZjzmjMonthDTO> monthCountMap = monthCounts.stream()
                .collect(Collectors.toMap(ZjzmjMonthDTO::getKey, dto -> dto));
        for (int month = 1; month <= 12; month++) {
            result.add(monthCountMap.getOrDefault(month, new ZjzmjMonthDTO(month, 0.0)));
        }
        return result;
    }

    @Override
    public List<DsMonthDTO> getMonthCountForDs(MonthCountQueryParam queryParam) {
        List<DsMonthDTO> result = new ArrayList<>();
        List<DsMonthDTO> monthCounts;
        if (queryParam.getTableName().equals(TableNameEnum.PROJECT_ACCEPTANCE)) {
            QueryWrapper<ProjectAcceptanceEntity> queryWrapper = buildQueryWrapper(queryParam, ProjectAcceptanceEntity.class, false);
            queryWrapper.select("EXTRACT(MONTH FROM wcsj) AS key", "count(1) as value");
            queryWrapper.apply(StrUtil.format("EXTRACT(YEAR FROM wcsj) = {}", queryParam.getYear()));
            queryWrapper.groupBy("key");
            monthCounts = mapper.selectMaps(queryWrapper).stream()
                    .map(map -> new DsMonthDTO(((Number) map.get("key")).intValue(), (Long) map.get("value")))
                    .collect(Collectors.toList());
        } else {
            QueryWrapper<ProjectAchievementEntity> queryWrapper = buildQueryWrapper(queryParam, ProjectAchievementEntity.class, false);
            queryWrapper.select("EXTRACT(MONTH FROM wcsj) AS key", "count(1) as value");
            queryWrapper.apply(StrUtil.format("EXTRACT(YEAR FROM wcsj) = {}", queryParam.getYear()));
            queryWrapper.groupBy("key");
            monthCounts = achievementMapper.selectMaps(queryWrapper).stream()
                    .map(map -> new DsMonthDTO(((Number) map.get("key")).intValue(), (Long) map.get("value")))
                    .collect(Collectors.toList());
        }

        Map<Integer, DsMonthDTO> monthCountMap = monthCounts.stream()
                .collect(Collectors.toMap(DsMonthDTO::getKey, dto -> dto));
        for (int month = 1; month <= 12; month++) {
            result.add(monthCountMap.getOrDefault(month, new DsMonthDTO(month, 0)));
        }
        return result;
    }

    @Override
    public List<ZygnGroupCountDTO> getZygnGroupCount(ProjectResultQueryParam queryParam) {
        List<ZygnGroupCountDTO> zygnGroupCountDTOS = new ArrayList<>();
        if (queryParam.getTableName().equals(TableNameEnum.PROJECT_ACCEPTANCE)) {
            QueryWrapper<ProjectAcceptanceEntity> queryWrapper = buildQueryWrapper(queryParam, ProjectAcceptanceEntity.class, false);
            queryWrapper.select("gnlb", "sum(zygnjzmj) as zygnjzmj_sum").groupBy("gnlb");
            mapper.selectMaps(queryWrapper).forEach(map -> {
                ZygnGroupCountDTO zygnGroupCountDTO = new ZygnGroupCountDTO();
                zygnGroupCountDTO.setKey((String) map.get("gnlb"));
                zygnGroupCountDTO.setValue((Double) map.get("zygnjzmj_sum"));
                zygnGroupCountDTOS.add(zygnGroupCountDTO);
            });
        } else {
            QueryWrapper<ProjectAchievementEntity> queryWrapper = buildQueryWrapper(queryParam, ProjectAchievementEntity.class, false);
            queryWrapper.select("gnlb", "sum(zygnjzmj) as zygnjzmj_sum").groupBy("gnlb");
            achievementMapper.selectMaps(queryWrapper).forEach(map -> {
                ZygnGroupCountDTO zygnGroupCountDTO = new ZygnGroupCountDTO();
                zygnGroupCountDTO.setKey((String) map.get("gnlb"));
                zygnGroupCountDTO.setValue((Double) map.get("zygnjzmj_sum"));
                zygnGroupCountDTOS.add(zygnGroupCountDTO);
            });
        }
        return zygnGroupCountDTOS;
    }

    @Override
    public List<ZjzmjCountGroupByLgDTO> getZjzmjCountGroupByLg(ProjectResultQueryParam queryParam) {
        String caseStatement = "CASE " +
                "WHEN jzgd <= 24 THEN '小于等于24米' " +
                "WHEN jzgd > 24 AND jzgd <= 60 THEN '24米-60米' " +
                "WHEN jzgd > 60 AND jzgd <= 80 THEN '60米-80米' " +
                "WHEN jzgd > 80 AND jzgd <= 100 THEN '80米-100米' " +
                "ELSE '大于100米' END AS lg_type";

        List<Map<String, Object>> queryResults;
        if (queryParam.getTableName().equals(TableNameEnum.PROJECT_ACCEPTANCE)) {
            QueryWrapper<ProjectAcceptanceEntity> queryWrapper = buildQueryWrapper(queryParam, ProjectAcceptanceEntity.class, false);
            queryWrapper.select(caseStatement,
                    "SUM(zjzmj) AS value",
                    "COUNT(1) AS key");
            queryWrapper.groupBy("lg_type");
            queryResults = mapper.selectMaps(queryWrapper);
        } else {
            QueryWrapper<ProjectAchievementEntity> queryWrapper = buildQueryWrapper(queryParam, ProjectAchievementEntity.class, false);
            queryWrapper.select(caseStatement,
                    "SUM(zjzmj) AS value",
                    "COUNT(1) AS key");
            queryWrapper.groupBy("lg_type");
            queryResults = achievementMapper.selectMaps(queryWrapper);
        }

        // 定义所有可能的高度区间
        List<String> allHeightRanges = Arrays.asList(
                "小于等于24米", "24米-60米", "60米-80米", "80米-100米", "大于100米"
        );

        // 将查询结果转换为Map，以lg_type为键
        Map<String, ZjzmjCountGroupByLgDTO> resultMap = queryResults.stream()
                .collect(Collectors.toMap(
                        map -> (String) map.get("lg_type"),
                        map -> new ZjzmjCountGroupByLgDTO(
                                (String) map.get("lg_type"),
                                ((Number) map.get("value")).doubleValue(),
                                ((Number) map.get("key")).longValue()
                        )
                ));

        // 确保所有区间都有值，如果没有则设置默认值
        return allHeightRanges.stream()
                .map(range -> resultMap.getOrDefault(range, new ZjzmjCountGroupByLgDTO(range, 0.0, 0L)))
                .collect(Collectors.toList());
    }

    @Override
    public List<ZjzmjCountGroupByCsDTO> getZjzmjCountGroupByCs(ProjectResultQueryParam queryParam) {
        if (queryParam.getTableName().equals(TableNameEnum.PROJECT_ACCEPTANCE)) {
            QueryWrapper<ProjectAcceptanceEntity> queryWrapper = buildQueryWrapper(queryParam, ProjectAcceptanceEntity.class, false);
            queryWrapper.select("CASE WHEN dscs BETWEEN 1 AND 3 THEN '低层' WHEN dscs BETWEEN 4 AND 6 THEN '多层' WHEN dscs BETWEEN 7 AND 9 THEN '中高层'  ELSE '高层' END AS cs_type", "COUNT(1) AS key", "SUM(zjzmj) AS value");
            queryWrapper.groupBy("cs_type");
            return mapper.selectMaps(queryWrapper).stream()
                    .map(map -> new ZjzmjCountGroupByCsDTO((Long) map.get("key"), (Double) map.get("value"), (String) map.get("cs_type")))
                    .collect(Collectors.toList());
        } else {
            QueryWrapper<ProjectAchievementEntity> queryWrapper = buildQueryWrapper(queryParam, ProjectAchievementEntity.class, false);
            queryWrapper.select("CASE WHEN dscs BETWEEN 1 AND 3 THEN '低层' WHEN dscs BETWEEN 4 AND 6 THEN '多层' WHEN dscs BETWEEN 7 AND 9 THEN '中高层'  ELSE '高层' END AS cs_type", "COUNT(1) AS key", "SUM(zjzmj) AS value");
            queryWrapper.groupBy("cs_type");
            return achievementMapper.selectMaps(queryWrapper).stream()
                    .map(map -> new ZjzmjCountGroupByCsDTO((Long) map.get("key"), (Double) map.get("value"), (String) map.get("cs_type")))
                    .collect(Collectors.toList());
        }

    }

    @Override
    public List<Integer> getYearsForWcsj(ProjectResultQueryParam queryParam) {
        if (queryParam.getTableName().equals(TableNameEnum.PROJECT_ACCEPTANCE)) {
            QueryWrapper<ProjectAcceptanceEntity> queryWrapper = buildQueryWrapper(queryParam, ProjectAcceptanceEntity.class, false);
            queryWrapper.select("DISTINCT EXTRACT(YEAR FROM wcsj) AS year");
            queryWrapper.apply("wcsj IS NOT NULL");
            queryWrapper.orderByDesc("year");
            return mapper.selectObjs(queryWrapper).stream().map(m -> ((Number) m).intValue()).collect(Collectors.toList());
        } else {
            QueryWrapper<ProjectAchievementEntity> queryWrapper = buildQueryWrapper(queryParam, ProjectAchievementEntity.class, false);
            queryWrapper.select("DISTINCT EXTRACT(YEAR FROM wcsj) AS year");
            queryWrapper.apply("wcsj IS NOT NULL");
            queryWrapper.orderByDesc("year");
            return achievementMapper.selectObjs(queryWrapper).stream().map(m -> ((Number) m).intValue()).collect(Collectors.toList());
        }
    }

    @Override
    public List<BaseKeyValueDTO> getProjectRangeGeometry(ProjectResultQueryParam queryParam) {
        if (queryParam.getTableName().equals(TableNameEnum.PROJECT_ACCEPTANCE)) {
            QueryWrapper<ProjectAcceptanceEntity> queryWrapper = buildQueryWrapper(queryParam, ProjectAcceptanceEntity.class, true);
            queryWrapper.select("smid", "ST_AsGeoJSON(smgeometry) as smgeometry");
            List<ProjectAcceptanceEntity> entities = mapper.selectList(queryWrapper);
            return entities.stream().map(entity -> {
                BaseKeyValueDTO<Integer, String> baseKeyValueDTO = new BaseKeyValueDTO<>();
                baseKeyValueDTO.setKey(entity.getSmid());
                baseKeyValueDTO.setValue(entity.getSmgeometry());
                return baseKeyValueDTO;
            }).collect(Collectors.toList());

        } else if (queryParam.getTableName().equals(TableNameEnum.PROJECT_ACHIEVEMENT)) {
            QueryWrapper<ProjectAchievementEntity> queryWrapper = buildQueryWrapper(queryParam, ProjectAchievementEntity.class, true);
            queryWrapper.select("smid", "ST_AsGeoJSON(smgeometry) as smgeometry");
            List<ProjectAchievementEntity> entities = achievementMapper.selectList(queryWrapper);
            return entities.stream().map(entity -> {
                BaseKeyValueDTO<Integer, String> baseKeyValueDTO = new BaseKeyValueDTO<>();
                baseKeyValueDTO.setKey(entity.getSmid());
                baseKeyValueDTO.setValue(entity.getSmgeometry());
                return baseKeyValueDTO;
            }).collect(Collectors.toList());

        } else {
            QueryWrapper<ProjectAchievementGeneralPlanEntity> queryWrapper = buildQueryWrapper(queryParam, ProjectAchievementGeneralPlanEntity.class, true);
            queryWrapper.select("smid", "ST_AsGeoJSON(smgeometry) as smgeometry");
            List<ProjectAchievementGeneralPlanEntity> entities = generalPlanMapper.selectList(queryWrapper);
            return entities.stream().map(entity -> {
                BaseKeyValueDTO<Integer, String> baseKeyValueDTO = new BaseKeyValueDTO<>();
                baseKeyValueDTO.setKey(entity.getSmid());
                baseKeyValueDTO.setValue(entity.getSmgeometry());
                return baseKeyValueDTO;
            }).collect(Collectors.toList());
        }
    }

    @Override
    public <T> T getDataForSmid(GetDataQueryParam queryParam) {
        if (TableNameEnum.PROJECT_ACCEPTANCE.equals(queryParam.getTableName())) {
            LambdaQueryWrapper<ProjectAcceptanceEntity> queryWrapper = new LambdaQueryWrapper<>();
            queryWrapper.eq(ProjectAcceptanceEntity::getSmid, queryParam.getSmid());
            ProjectAcceptanceEntity entity = mapper.selectOne(queryWrapper);
            if (entity != null) {
                return (T) po2Dto(entity, ProjectAcceptanceDTO.class);
            }
        } else if (TableNameEnum.PROJECT_ACHIEVEMENT.equals(queryParam.getTableName())) {
            LambdaQueryWrapper<ProjectAchievementEntity> queryWrapper = new LambdaQueryWrapper<>();
            queryWrapper.eq(ProjectAchievementEntity::getSmid, queryParam.getSmid());
            ProjectAchievementEntity entity = achievementMapper.selectOne(queryWrapper);
            if (entity != null) {
                return (T) po2Dto(entity, ProjectAcceptanceDTO.class);
            }
        } else {
            LambdaQueryWrapper<ProjectAchievementGeneralPlanEntity> queryWrapper = new LambdaQueryWrapper<>();
            queryWrapper.eq(ProjectAchievementGeneralPlanEntity::getSmid, queryParam.getSmid());
            ProjectAchievementGeneralPlanEntity entity = generalPlanMapper.selectOne(queryWrapper);
            if (entity != null) {
                return (T) po2Dto(entity, ProjectAchievementGeneralPlanDTO.class);
            }
        }
        return null;
    }

    @Override
    public List<Double> getMasterPlanStatistics(ProjectResultQueryParam queryParam) {
        QueryWrapper<ProjectAchievementGeneralPlanEntity> queryWrapper = buildQueryWrapper(queryParam, ProjectAchievementGeneralPlanEntity.class, true);
        queryWrapper.select("sum(dkzjzmj) as total_construction_area", "sum(dkzjrmj) as total_floor_area");

        Map<String, Object> result = generalPlanMapper.selectMaps(queryWrapper).get(0);

        List<Double> statistics = new ArrayList<>();
        statistics.add(result.get("total_construction_area") == null ? 0.0 : ((Number) result.get("total_construction_area")).doubleValue());
        statistics.add(result.get("total_floor_area") == null ? 0.0 : ((Number) result.get("total_floor_area")).doubleValue());

        return statistics;
    }

    @Override
    public List<MasterPlanMonthDTO> getMasterPlanMonthStatistics(MonthCountQueryParam queryParam) {

        QueryWrapper<ProjectAchievementGeneralPlanEntity> queryWrapper = buildQueryWrapper(queryParam, ProjectAchievementGeneralPlanEntity.class, false);
        queryWrapper.select("EXTRACT(MONTH FROM wcsj) AS month", "sum(dkzjzmj) as dkzjzmj_sum", "count(1) as count");
        queryWrapper.apply(StrUtil.format("EXTRACT(YEAR FROM wcsj) = {}", queryParam.getYear()));
        queryWrapper.groupBy("month");
        List<MasterPlanMonthDTO> monthCounts = generalPlanMapper.selectMaps(queryWrapper).stream()
                .map(map -> new MasterPlanMonthDTO(
                        ((Number) map.get("month")).intValue(),
                        ((Number) map.get("dkzjzmj_sum")).doubleValue(),
                        ((Number) map.get("count")).longValue()
                ))
                .toList();

        Map<Integer, MasterPlanMonthDTO> monthCountMap = monthCounts.stream()
                .collect(Collectors.toMap(MasterPlanMonthDTO::getMasterPlanMonth, dto -> dto));

        List<MasterPlanMonthDTO> result = new ArrayList<>();
        for (int month = 1; month <= 12; month++) {
            result.add(monthCountMap.getOrDefault(month, new MasterPlanMonthDTO(month, 0.0, 0L)));
        }
        return result;
    }

    @Override
    public List<MasterPlanDKCountDTO> getMasterPlanCountByDistrict(ProjectResultQueryParam queryParam) {
        List<BaiYunRegionAttrDTO> attrFromShp = GisUtil.getAttrFromGeoJson(ResourceUtil.getStream(shpPath), BaiYunRegionAttrDTO.class);

        return attrFromShp.stream().map(attr -> {
            MasterPlanDKCountDTO masterPlanDKCountDTO = new MasterPlanDKCountDTO();
            BeanUtil.copyProperties(attr, masterPlanDKCountDTO, "geometry");
            QueryWrapper<ProjectAchievementGeneralPlanEntity> queryWrapper = buildQueryWrapper(queryParam, ProjectAchievementGeneralPlanEntity.class, false);
            // 不设置默认值会导致map查询为空
            queryWrapper.select("COALESCE(sum(dkzjzmj), 0) as dkzjzmj_sum");
            queryWrapper.apply(StrUtil.format("ST_Intersects(smgeometry, ST_GeomFromText('{}',{}))", attr.getGeometry(), 32768));
            generalPlanMapper.selectMaps(queryWrapper).forEach(map -> {
                if (map != null) {
                    {
                        masterPlanDKCountDTO.setDkzjzmjSum((Double) map.get("dkzjzmj_sum"));
                    }
                }
            });
            return masterPlanDKCountDTO;
        }).collect(Collectors.toList());

    }

    @Override
    public List<BaseKeyValueDTO<String, Integer>> getAirportHeightRestrictionAnalysis(ProjectResultQueryParam queryParam) {
        QueryWrapper<ProjectAchievementGeneralPlanEntity> queryWrapper = buildQueryWrapper(queryParam, ProjectAchievementGeneralPlanEntity.class, false);
        queryWrapper.select("sfsjjcxg as key", "COUNT(*) as value")
                .isNotNull("sfsjjcxg")
                .groupBy("sfsjjcxg")
                .orderByDesc("value");
        List<Map<String, Object>> results = generalPlanMapper.selectMaps(queryWrapper);

        return results.stream()
                .map(map -> {
                    String key = (String) map.get("key");
                    Integer value = ((Number) map.get("value")).intValue();
                    return new BaseKeyValueDTO<>(key, value);
                })
                .collect(Collectors.toList());
    }
}
